package com.litidea.dao.abs;

import static com.litidea.dao.sqls.SystemDAOSqls.*;
import static com.litidea.commons.keys.SystemKeys.*;
import static com.litidea.commons.dict.StaticDics.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.litidea.commons.utils.CollectionUtils;
import com.litidea.dao.BaseDAO;
import com.litidea.dataobjects.DepartmentDO;
import com.litidea.dataobjects.MenuNodeDO;
import com.litidea.dataobjects.RoleDO;
import com.litidea.dataobjects.UserDO;
@SuppressWarnings("unchecked")
public class SystemDAOAbs extends BaseDAO {
	
	/**
     * 填充一个menu对象
     * @param rs
     * @return
     * @throws SQLException
     */
    protected MenuNodeDO getOneMenu(ResultSet rs) throws SQLException {
        MenuNodeDO menuNodeDO = new MenuNodeDO();
        menuNodeDO.setId(rs.getInt("id"));
        menuNodeDO.setText(rs.getString("name"));
        menuNodeDO.setParentId(rs.getInt("parent_id"));
        menuNodeDO.setLevelId(rs.getInt("level_id"));
        menuNodeDO.setAllParentId(rs.getString("all_parent_id"));
        menuNodeDO.setMenuDesc(rs.getString("menu_desc"));
        menuNodeDO.setClickType(rs.getInt("click_type"));
        menuNodeDO.setUrl(rs.getString("url"));
        menuNodeDO.setTarget(rs.getString("target"));
        menuNodeDO.setFunctionName(rs.getString("func_name"));
        menuNodeDO.setObjectId(rs.getString("object_id"));
        menuNodeDO.setMutiObject(rs.getInt("is_muti_object"));
        menuNodeDO.setRefresh(rs.getInt("is_refresh"));
        menuNodeDO.setJsFiles(rs.getString("js_files"));
        menuNodeDO.setLocationKey(rs.getString("location_key"));
        menuNodeDO.setOrderId(rs.getInt("order_id"));
        menuNodeDO.setBusiKey(rs.getString("busi_key"));
        menuNodeDO.setMenuType(rs.getInt("menu_type"));
        menuNodeDO.setExpandChild(rs.getInt("expand_child"));
        menuNodeDO.setSysName(rs.getString("sys_name"));
        menuNodeDO.setCanDel(rs.getInt("can_del"));
        return menuNodeDO;
    }
    
    
    /**
     * 填充一个menu对象
     * @param rs
     * @return
     * @throws SQLException
     */
    protected DepartmentDO getOneDept(ResultSet rs) throws SQLException {
    	return new DepartmentDO(
    			rs.getInt("id") , rs.getString("dept_name") , rs.getString("dept_full_name") , 
    			rs.getInt("parent_id") , rs.getString("all_parent_id") , rs.getString("dept_responsible"),
    			rs.getString("dept_desc") , rs.getInt("level_id") , rs.getString("create_date"),
    			rs.getString("dept_create_date") , rs.getString("chg_date") ,
    			rs.getInt("order_id"),rs.getString("dept_type"),rs.getInt("deptMgr"), rs.getString("deptMgrName")
    	);
    }
    
    /**
     * 移动到某个节点之前
     * @param nowDept
     * @param compDept
     * @return
     */
    public String moveToBefore(DepartmentDO nowDept , DepartmentDO compDept) {
    	//修改顺序号
    	if(compDept.getLevelId() == 1) {
    		jdbcTemplate.update(ADD_1_AFTER_FIRST_LEVEL , new Object[] { compDept.getOrderId()});
    	}else {
    		jdbcTemplate.update(ADD_1_AFTER_ORDER , new Object[] { compDept.getParentId(), compDept.getOrderId()});
    	}
    	String allParentId = compDept.getAllParentId();
    	String deptFullName = compDept.getDeptFullName();
    	
    	allParentId = allParentId.replace("{" + compDept.getId() + "}", "{" + nowDept.getId() + "}");
    	deptFullName = deptFullName.substring(0 , deptFullName.lastIndexOf(compDept.getDeptName())) + nowDept.getDeptName();
    	jdbcTemplate.update(UPDATE_DEPT_INFO , new Object[] {
    			compDept.getOrderId() , allParentId , compDept.getLevelId() , deptFullName,compDept.getParentId(),nowDept.getId()
    	});
    	moveCascade(nowDept , allParentId , deptFullName , compDept.getLevelId() - nowDept.getLevelId());
    	return allParentId;
    }
    
    /**
     * 移动到某个节点之后
     * @param nowDept
     * @param compDept
     * @return
     */
    public String moveToAfter(DepartmentDO nowDept , DepartmentDO compDept) {
    	if(compDept.getLevelId() == 1) {
    		jdbcTemplate.update(ADD_2_AFTER_FIRST_LEVEL , new Object[] { compDept.getOrderId()});
    	}else {
    		jdbcTemplate.update(ADD_2_AFTER_ORDER , new Object[] { compDept.getParentId(), compDept.getOrderId()});
    	}
    	String allParentId = compDept.getAllParentId();
    	String deptFullName = compDept.getDeptFullName();
    	
    	allParentId = allParentId.replace("{" + compDept.getId() + "}", "{" + nowDept.getId() + "}");
    	deptFullName = deptFullName.substring(0 , deptFullName.lastIndexOf(compDept.getDeptName())) + nowDept.getDeptName();
    	jdbcTemplate.update(UPDATE_DEPT_INFO , new Object[] {
    			compDept.getOrderId() + 1 , allParentId , compDept.getLevelId() , deptFullName,compDept.getParentId(),nowDept.getId()
    	});
    	moveCascade(nowDept , allParentId , deptFullName , compDept.getLevelId() - nowDept.getLevelId());
    	return allParentId;
    }
    
    /**
     * 移动到子部门
     * @param nowDept
     * @param compDept
     * @return
     */
    public String moveToChild(DepartmentDO nowDept , DepartmentDO compDept) {
    	String allParentId = compDept.getAllParentId() + "{" + nowDept.getId() + "}";
    	String deptFullName = compDept.getDeptFullName() + "-" + nowDept.getDeptName();
    	String parentId = compDept.getId().toString();
    	int level = compDept.getLevelId() + 1;
    	jdbcTemplate.update(UPDATE_DEPT_INFO , new Object[] {
    			getChildMaxOrderId(parentId) + 1 , allParentId , level , 
    			deptFullName , parentId , nowDept.getId()
    	});
    	moveCascade(nowDept , allParentId , deptFullName , level - nowDept.getLevelId());
    	return allParentId;
    }
    
    /**
     * 级联移动子节点
     * @param nowDept
     * @param newAllParentId
     * @param newDeptFullName
     * @return
     */
    protected void moveCascade(DepartmentDO nowDept , String newAllParentId , String newDeptFullName ,int levelDif) {
    	jdbcTemplate.update(MOVE_CASCADE_SQL , new Object[] {
    			nowDept.getAllParentId() , newAllParentId,
    			nowDept.getDeptFullName() , newDeptFullName,
    			levelDif , nowDept.getAllParentId()+"%" , nowDept.getId()
    	});
    }
    
    /**
     * 获取下一个用户ID
     * @return
     */
    public Integer getNextUserId() {
    	return jdbcTemplate.queryForInt(GET_USER_SEQ_SQL);
    }
    
    /**
     * 获取下一个部门ID
     * @return
     */
    public Integer getNextDeptId() {
    	return jdbcTemplate.queryForInt(GET_DEPT_SEQ_SQL) + 1;
    }
    
    /**
     * 获取某个节点是否包含子节点
     * @param id
     * @return
     */
    public boolean getHasChildNum(String id) {
    	return jdbcTemplate.queryForInt(GET_COUNTER_BY_PARENT , new Object[] {id}) > 0;
    }
    
    /**
     * 获取是否包含人员信息
     * @param id
     * @return
     */
    public boolean getHashMenmbers(String id) {
    	/*
    	 * 人员信息尚未开始完成，此部分暂时保留为空
    	 * */
    	return false;
    }
    
    /**
     * 获取某节点的子节点最大的ID
     * @param id
     * @return
     */
    public Integer getChildMaxOrderId(String id) {
    	if(StringUtils.isEmpty(id)) {
    		return jdbcTemplate.queryForInt(GET_MAX_ORDER_FIRST_LEVEL);
    	}else {
    		return jdbcTemplate.queryForInt(GET_MAX_ORDER_BY_PARENT , new Object[] {id});
    	}
    }
    
    /**
     * 获取最大的ROLE_ORDER
     */
    public Integer getMaxRoleOrder() {
    	return jdbcTemplate.queryForInt(GET_MAX_ROLE_ORDER);
    }
    
    /**
     * 获取最大的ROLE_Id
     */
    public Integer getMaxRoleId() {
    	return jdbcTemplate.queryForInt(GET_MAX_ROLE_ID);
    }
    
    /**
     * 获取最大的用户ORDER
     */
    public Integer getMaxUserOrder() {
    	return jdbcTemplate.queryForInt(GET_MAX_USER_ORDER);
    }
    
    /**
     * 获取一个Role节点
     */
    public RoleDO getOneRole(ResultSet rs) throws SQLException {
    	return new RoleDO(
    		rs.getInt("id") , rs.getString("name") , rs.getString("role_responsible") ,
    		rs.getInt("menu_nums") , rs.getString("create_date") , rs.getString("chg_date") ,
    		rs.getInt("members") , rs.getString("role_key")
    	);
    }
    
    /**
     * 获取role的分页列表
     * @return
     */
    public List<RoleDO> getRoles(Map<String ,String>map) {
    	StringBuilder sqlbuf = new StringBuilder(1024).append(SEARCH_ROLE_START);
    	List<Object>params = new ArrayList<Object>(5);
    	roleDynamicSearch(map, sqlbuf, params);
    	sqlbuf.append(SEARCH_ROLE_END);
    	appendPageParam(map, params);
    	return jdbcTemplate.query(sqlbuf.toString() , params.toArray(), new RowMapper() {
    		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    			return getOneRole(rs);
    		}
    	});
    }
    
    /**
     * 获取role的大小
     * @param map
     * @return
     */
    public int getRoleCount(Map<String ,String>map) {
    	StringBuilder sqlbuf = new StringBuilder(512).append(SEARCH_ROLE_COUNT);
    	List<Object>params = new ArrayList<Object>(2);
    	roleDynamicSearch(map, sqlbuf, params);
    	return jdbcTemplate.queryForInt(sqlbuf.toString() , params.toArray());
    }

    /**
     * 查询role的动态条件
     * @param map
     * @param sqlbuf
     * @param params
     */
	private void roleDynamicSearch(Map<String, String> map,
			StringBuilder sqlbuf, List<Object> params) {
		this.appendSql(sqlbuf, map.get("name"), " and name like ?", params , true);
    	this.appendSql(sqlbuf, map.get("responsible"), " and role_responsible like ?", params , true);
	}
	
	/**
	 * 获取所有的Parent
	 * @param allParent
	 * @return
	 */
	protected String[] getAllparentIds(String allParent) {
		if(StringUtils.isEmpty(allParent)) return EMPTY_STRING_ARRAY;
		return allParent.substring(1, allParent.length() - 1).split("\\}\\{");
	}
	
	/**
	 * 填充父亲类
	 * @param map
	 * @param allParent
	 */
	protected void fillAllParentId(Map<String , Integer> map , String allParent) {
		String[] allParentIds = getAllparentIds(allParent);
		for(String parentId : allParentIds) {
			map.put(parentId, null);
		}
	}
	
	/**
	 * 获取用户列表
	 * @param map
	 * @return
	 *
	 */
	protected List<UserDO> getUsers(Map<String ,String>map) {
		StringBuilder sqlbuf = new StringBuilder(1024).append(SEARCH_USER_START);
		List<Object>params = new ArrayList<Object>(7);
		userDynamicSearch(map, sqlbuf, params);
		appendPageParam(map, params);
		sqlbuf.append(SEARCH_USER_END);
		return jdbcTemplate.query(sqlbuf.toString() , params.toArray() , new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return getOneUserDO(rs);
			}
		});
	}
	
	/**
	 * 获取数据量
	 * @param map
	 * @return
	 */
	protected int getUserCount(Map<String ,String>map) {
		StringBuilder sqlbuf = new StringBuilder(512).append(SEARCH_USER_COUNT);
    	List<Object>params = new ArrayList<Object>(2);
    	userDynamicSearch(map, sqlbuf, params);
    	return jdbcTemplate.queryForInt(sqlbuf.toString() , params.toArray());
	}
	
	/**
	 * 获取用户信息通过ID
	 * @param userId
	 * @return
	 */
	protected UserDO getUserById(String userId) {
		List<UserDO>list = jdbcTemplate.query(SEARCH_USER_BASE  + ",c.DEPT_NAME as deptName from "+ USER_TABLE + " a," + DEPT_TBALE + " c where a.dept_id=c.id and a.id=?", 
				new Object[] {userId} , new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return getOneUserDO(rs);
			}
		});
		return (UserDO)CollectionUtils.getFirstValue(list);
	}

	/**
	 * 用户动态查询
	 * @param map
	 * @param sqlbuf
	 * @param params
	 */
	private void userDynamicSearch(Map<String, String> map,
			StringBuilder sqlbuf, List<Object> params) {
		this.appendSql(sqlbuf, map.get("name"), " and name like ?", params , true);
		this.appendSql(sqlbuf, map.get("gender"), " and gender = ?", params);
		this.appendSql(sqlbuf, map.get("idCard"), " and id_card like ?", params , true);
		this.appendSql(sqlbuf, map.get("deptName"), " and dept_id in(select id from " + DEPT_TBALE + " where dept_name like ?)", params , true);
		this.appendSql(sqlbuf, map.get("email"), " and email like ?", params , true);
		this.appendSql(sqlbuf, map.get("birthday"), " and birthday = ?", params , false);
	}

	/**
	 * 填充一个UserDO
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	protected Object getOneUserDO(ResultSet rs) throws SQLException {
		Integer gender = rs.getInt("gender");
		String genderStr = GENDER_MAP.get(gender);
		return new UserDO(
				rs.getInt("id") ,rs.getString("name"),rs.getString("id_card"),
				rs.getString("login_name") , rs.getString("email"),rs.getString("tel"),
				rs.getString("tel2"),rs.getString("user_content"),
				rs.getString("certificate"),rs.getString("user_desc"),gender,
				genderStr,rs.getString("birthDay"),rs.getInt("dept_id") , 
				rs.getString("deptName") , rs.getString("mobile"),
				rs.getString("before_login") , rs.getString("last_login"),
				rs.getString("before_login_ip") , rs.getString("last_login_ip")
		);
	}
	
	/**
	 * 获取一个用户信息，并填充了password
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	protected Object getOneUserAndPassDO(ResultSet rs) throws SQLException {
		UserDO userDO = (UserDO)getOneUserDO(rs);
		userDO.setUserPass(rs.getString("login_pass"));
		return userDO;
	}
	
	/**
     * 校验用户是否存在
     * @param loginName
     */
    protected void checkUserExists(String loginName) {
    	if(loginName == null) throw new RuntimeException("登录名为空，创建用户失败。");
    	int exists = jdbcTemplate.queryForInt(GET_USER_EXISTS , new Object[] {loginName});
    	if(exists > 0) throw new RuntimeException("该登录名已经被使用，请从新输入再提交。");
    }
}
